/*
 *  mini-SaaS
 *  Application for developing Software as a Service (SaaS) 
 *  Copyright (c) 2011 Anand Sudhakaran, Bryce Holton, Chao Zhang
 *  ASU
 *
 *  This file is part of mini-SaaS.
 *
 *  mini-SaaS is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 3 of the License, or
 *  (at your option) any later version.
 *  
 *  mini-SaaS is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.

 *  You should have received a copy of the GNU General Public License
 *  along with mini-SaaS; if not, see <http://www.gnu.org/licenses/>. 
 *
 */
package loginbean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Vector;

public class WorkFlow 
{
	private static Vector<String> queryDataBase(String query)
	{
		Vector<String> results = new Vector<String>();
		try
		{
			DBManager db = new DBManager();
			Connection conn = db.getDatabaseConnection();
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(query);
			while(rs.next())
			{
				results.addElement(rs.getString(1));
			}
			conn.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return results;
	}
	private static void insertIntoDataBase(String query)
	{
		try
		{
			DBManager db = new DBManager();
			Connection conn = db.getDatabaseConnection();
			Statement st = conn.createStatement();
			st.execute(query);
			conn.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	private static int getWorkFlowTenantId(String workFlowName)
	{
		String strQuery = "select distinct(tenantid) from workflowtable where workflowname='" + workFlowName + "'";
		int workFlowTenantId = new Integer(queryDataBase(strQuery).get(0)).intValue();
		return workFlowTenantId;
	}
	private static String createUserWorkFlow(String workFlowName, int tenantid)
	{
		//get the old workflow, change the name and insert it into the database.
		String oldWorkFlowQuery = "select webserviceid from workflowtable where tenantid=1 and workflowname='" + workFlowName + "'";
		Vector<String> webSvcIds = queryDataBase(oldWorkFlowQuery);
		oldWorkFlowQuery = "select wsnumber from workflowtable where tenantid=1 and workflowname='" + workFlowName + "'";
		Vector<String> webSvcNums = queryDataBase(oldWorkFlowQuery);
		oldWorkFlowQuery = "select tenantname from tenanttable where tenantid=" + tenantid;
		String tenantName = queryDataBase(oldWorkFlowQuery).get(0);
		workFlowName = tenantName + "_" + workFlowName;
		String insertNewWorkFlowQuery;
		for(int i = 0; i < webSvcIds.size(); i ++)
		{
			insertNewWorkFlowQuery = "insert into workflowtable (wsnumber,workflowname,createdate,tenantid,webserviceid) values (";
			insertNewWorkFlowQuery += webSvcNums.get(i) + ",'" + workFlowName + "',CURRENT_TIMESTAMP," + tenantid + "," + webSvcIds.get(i) + ")";
			insertIntoDataBase(insertNewWorkFlowQuery);
		}
		return workFlowName;
	}
	public static String getAllWebServices(int tenantid)
	{
		String result = "<SELECT name='services'>";
		String strQuery = "select webservicename from webservicetable where (tenantid=1 or tenantid=" + tenantid + ")";
		Vector<String> serviceNames = queryDataBase(strQuery);
		for(int i = 0; i < serviceNames.size(); i ++)
		{
			result += "<option>"+ serviceNames.elementAt(i);
		}
		result += "</SELECT>";
		return result;
	}
	public static String getAllWorkFlows(int tenantid) throws SQLException
	{
		String result = "<SELECT name='flows'>";
		String strQuery = "select distinct(workflowname) from workflowtable where (tenantid=1 or tenantid=" + tenantid + ")";
		Vector<String> flowNames = queryDataBase(strQuery);
		for(int i = 0; i < flowNames.size(); i ++)
		{
			result += "<option>"+ flowNames.elementAt(i);
		}
		result += "</SELECT>";
		return result;
	}
	public static Vector<String> retrieveFlowServices(int tenantid, String workFlowName)
	{
		String strQuery = "select webservicename from workflowtable,webservicetable where (workflowtable.tenantid=1 or workflowtable.tenantid=" + tenantid;
		strQuery += ") and workflowtable.webserviceid=webservicetable.webserviceid and workflowname='" + workFlowName + "' order by wsnumber";
		Vector<String> serviceNames = queryDataBase(strQuery);
		return serviceNames;
	}
	public static Vector<Integer> retrieveFlowOrder(int tenantid, String workFlowName)
	{
		String strQuery = "select wsnumber from workflowtable where (tenantid=1 or tenantid=" + tenantid;
		strQuery += ") and workflowname='" + workFlowName + "' ORDER BY wsnumber";
		Vector<String> serviceOrder = queryDataBase(strQuery);
		Vector<Integer> result = new Vector<Integer>();
		for(int i = 0; i < serviceOrder.size(); i ++)
		{
			result.addElement(new Integer(serviceOrder.get(i)));
		}
		return result;
	}
	public static void insertNewService(int tenantid, String workFlowName, String newServiceName)
	{
		if(getWorkFlowTenantId(workFlowName) == 1)
		{
			workFlowName = createUserWorkFlow(workFlowName, tenantid);
		}
		
		String strQuery = "select max(wsnumber) from workflowtable where (tenantid=1 or tenantid=" + tenantid;
		strQuery += ") and workflowname='" + workFlowName + "'";
		int maxSvcNum = new Integer(queryDataBase(strQuery).get(0)).intValue();
		strQuery = null;
		
		strQuery = "select webserviceid from webservicetable where (tenantid=1 or tenantid=" + tenantid + ") and ";
		strQuery += "webservicename='" + newServiceName + "'";
		int webSvcId = new Integer(queryDataBase(strQuery).get(0)).intValue();
		
		String strInsert = "insert into workflowtable (wsnumber,workflowname,createdate,tenantid,webserviceid) values (";
		strInsert += maxSvcNum + 1 + ",'" + workFlowName + "',CURRENT_TIMESTAMP," + tenantid + "," + webSvcId + ")";
		insertIntoDataBase(strInsert);
	}
	public static void deleteService(int tenantId, String workFlowName,int svcOrderNumber)
	{
		if(getWorkFlowTenantId(workFlowName) == 1)
		{
			workFlowName = createUserWorkFlow(workFlowName, tenantId);
		}
		
		String strDelete = "delete from workflowtable where tenantid=" + tenantId;
		strDelete += " and workflowname='" + workFlowName + "' and wsnumber=" + svcOrderNumber;
		insertIntoDataBase(strDelete);
	}
	public static void insertNewWorkFlow(int tenantid, String workFlowName, Vector<String> webSvcNames)
	{
		String strQuery, strInsert = null;
		for(int i = 0; i < webSvcNames.size(); i ++)
		{
			strQuery = "select webserviceid from webservicetable where (tenantid=1 or tenantid=" + tenantid + ") and ";
			strQuery += "webservicename='" + webSvcNames.elementAt(i) + "'";
			int webSvcId = new Integer(queryDataBase(strQuery).get(0)).intValue();
			strQuery = null;
			
			strInsert = "insert into workflowtable (wsnumber,workflowname,createdate,tenantid,webserviceid) values (";
			strInsert += i + 1 + ",'" + workFlowName + "',CURRENT_TIMESTAMP," + tenantid + "," + webSvcId + ")";
			insertIntoDataBase(strInsert);
			strInsert = null;
		}
	}
	public static int getMaxSvcNum(int tenantid, String workFlowName)
	{
		String strQuery = "select max(wsnumber) from workflowtable where (tenantid=1 or tenantid=" + tenantid + ")";
		strQuery += " and workflowname='" + workFlowName + "'";
		int highestSvcNum = new Integer(queryDataBase(strQuery).get(0)).intValue();
		return highestSvcNum;
	}
	public static void changeSvcOrder(int tenantId, String workFlowName, Vector<String[]> newOrder)
	{
		if(getWorkFlowTenantId(workFlowName) == 1)
		{
			workFlowName = createUserWorkFlow(workFlowName, tenantId);
		}
		
		String strQuery, strUpdate;
		for(int i = 0; i < newOrder.size(); i ++)
		{
			strQuery = "select webservicetable.webserviceid from webservicetable,workflowtable where workflowtable.tenantid=" + tenantId + " and ";
			strQuery += "webservicetable.webserviceid=workflowtable.webserviceid and webservicename='" + newOrder.get(i)[1] + "'";
			int webSvcId = new Integer(queryDataBase(strQuery).get(0)).intValue();
			strQuery = null;
			
			strUpdate = "update workflowtable set wsnumber="+ newOrder.get(i)[0] +" where tenantid=" + tenantId;
			strUpdate += " and workflowname='" + workFlowName + "' and webserviceid=" + webSvcId;
			insertIntoDataBase(strUpdate);
			strUpdate = null;
		}
	}
	public static int getServiceId(String serviceName)
	{
		String strQuery = "select webserviceid from webservicetable where webservicename = '" + serviceName + "'";
		return new Integer(queryDataBase(strQuery).get(0)).intValue();
	}
	public static String getNextServiceNames(int serviceId, WorkFlowBean wf)
	{
		String result = null;
		String strQuery = null;
		if(wf == null)
		{
			result = "Work Flow Name: <input type=\"text\" name=\"workflowname\" value=\"\" /><br>";
			result += "Select the next Web Service for your Work Flow based on our recommendation:<br>";
			ArrayList<Integer> serviceList = OntologyManager.getNextService(serviceId);
		    Iterator<Integer> itr = serviceList.iterator();
		    result += "Service 1: ";
		    strQuery = "select webservicename from webservicetable where webserviceid = " +  serviceId;
	    	result += queryDataBase(strQuery).get(0) + "<br>";
		    result += "<SELECT name='svcs'>";
		    while( itr.hasNext() )
		    {
		    	strQuery = "select webservicename from webservicetable where webserviceid = " +  itr.next();
		    	result += "<option>"+ queryDataBase(strQuery).get(0);
			}
			result += "</SELECT>";
		}
		else
		{
			result = "Select the next Web Service for your Work Flow called " + wf.getName() + ", based on our recommendation:<br>";
			for(int i = 0; i < wf.getServices().size(); i ++)
			{
				result += "Service "+ (i+1) +": ";
			    strQuery = "select webservicename from webservicetable where webserviceid = " +  wf.getServices().elementAt(i);
		    	result += queryDataBase(strQuery).get(0) + "<br>";
			}
			ArrayList<Integer> serviceList = OntologyManager.getNextService(serviceId);
		    Iterator<Integer> itr = serviceList.iterator();
		    result += "<SELECT name='svcs'>";
		    while( itr.hasNext() )
		    {
		    	strQuery = "select webservicename from webservicetable where webserviceid = " +  itr.next();
		    	result += "<option>"+ queryDataBase(strQuery).get(0);
			}
			result += "</SELECT>";
		}

		return result;
	}
	public static void insertNewWorkFlowWithIds(int tenantid, String workFlowName, Vector<Integer> webSvcIds)
	{
		String strInsert = null;
		for(int i = 0; i < webSvcIds.size(); i ++)
		{
			strInsert = "insert into workflowtable (wsnumber,workflowname,createdate,tenantid,webserviceid) values (";
			strInsert += i + 1 + ",'" + workFlowName + "',CURRENT_TIMESTAMP," + tenantid + "," + webSvcIds.elementAt(i) + ")";
			insertIntoDataBase(strInsert);
			strInsert = null;
		}
	}
	public static String getnextURL(String currentURL, String wfName)
	{
		String strQuery, result = null;
		if(currentURL == null)
		{
			strQuery = "select min(wsnumber) from workflowtable where workflowname='"+wfName+"'";
			int lowestSvcNum = new Integer(queryDataBase(strQuery).get(0)).intValue();
			strQuery = "select webserviceid from workflowtable where workflowname='"+wfName+"' and wsnumber="+lowestSvcNum;
			int svcIdNum = new Integer(queryDataBase(strQuery).get(0)).intValue();
			strQuery = "select webserviceURL from webservicetable where webserviceid="+svcIdNum;
			result = queryDataBase(strQuery).get(0);
		}
		else
		{
			strQuery = "select workflowtable.wsnumber from workflowtable,webservicetable where workflowtable.workflowname='"+wfName+"' and webservicetable.webserviceurl='"+currentURL+"' and webservicetable.webserviceid=workflowtable.webserviceid";
			int currentSvcNum = new Integer(queryDataBase(strQuery).get(0)).intValue();
			currentSvcNum ++;
			strQuery = "select webserviceid from workflowtable where workflowname='"+wfName+"' and wsnumber="+currentSvcNum;
			System.out.println(wfName);
			System.out.println(strQuery);
			Vector<String> test = queryDataBase(strQuery);
			if(test != null)
			{
				int newSvcId = new Integer(queryDataBase(strQuery).get(0)).intValue();
				strQuery = "select webserviceURL from webservicetable where webserviceid="+newSvcId;
				result = queryDataBase(strQuery).get(0);
			}
		}
		
		return result;
	}
}
